﻿using System;
using System.Collections.Generic;
using System.IO;
using System.Text;
using System.Text.RegularExpressions;
using NPOI.HSSF.UserModel;
using NPOI.HSSF.Util;
using NPOI.SS.UserModel;
using NPOI.SS.Util;

namespace AChance.Func
{
    public static class FunExcel
    {
        /// <summary>
        /// 工作簿配置
        /// </summary>
        public class WorkBook
        {
            private IWorkbook _iWorkBook = null;
            private ICellStyle _iCellStyle = null;
            public WorkBook()
            {
                _iWorkBook = new HSSFWorkbook();
                _iCellStyle = CreateDefaultStyle();
            }
            public WorkBook(string path)
            {
                _iWorkBook = WorkbookFactory.Create(new FileStream(path, FileMode.Open, FileAccess.ReadWrite));
                _iCellStyle = CreateDefaultStyle();
            }
            public WorkBook(Stream stream)
            {
                _iWorkBook = WorkbookFactory.Create(stream);
                _iCellStyle = CreateDefaultStyle();
            }
            public void Save(string path)
            {
                Stream stream = new FileStream(path, FileMode.OpenOrCreate, FileAccess.ReadWrite);
                _iWorkBook.Write(stream);
                stream.Close();
            }
            public void Save(Stream stream)
            {
                _iWorkBook.Write(stream);
            }
            /// <summary>
            ///  创建Sheet
            /// </summary>
            public WorkSheet CreateSheet()
            {
                return CreateSheet(null);
            }
            public WorkSheet CreateSheet(string sheetName)
            {
                WorkSheet workSheet = new WorkSheet();
                if (sheetName == null)
                    workSheet.ISheet = _iWorkBook.CreateSheet();
                else
                    workSheet.ISheet = _iWorkBook.CreateSheet(sheetName);
                workSheet.IWorkBook = _iWorkBook;
                workSheet.ICellStyle = _iCellStyle;
                workSheet.SetDefalutRowHeight(17);
                workSheet.SetDefaultColumnWidth(15);
                return workSheet;
            }
            /// <summary>
            ///  删除Sheet
            /// </summary>
            public void DeleteSheet(int sheetIndex)
            {
                _iWorkBook.RemoveSheetAt(sheetIndex);
            }
            /// <summary>
            ///  激活Sheet
            /// </summary>
            public void ActiveSheet(int sheetIndex)
            {
                _iWorkBook.SetActiveSheet(sheetIndex);
            }
            public void ActiveSheet(string sheetName)
            {
                _iWorkBook.SetActiveSheet(GetSheet(sheetName).GetIndex());
            }
            /// <summary>
            ///  整理Sheet
            /// </summary>
            public void OrderSheet(int sheetIndex, int order)
            {
                _iWorkBook.SetSheetOrder(GetSheet(sheetIndex).GetName(), order);
            }
            public void OrderSheet(string sheetName, int order)
            {
                _iWorkBook.SetSheetOrder(sheetName, order);
            }
            /// <summary>
            /// 访问Sheet
            /// </summary>
            public WorkSheet GetSheet(int sheetIndex)
            {
                WorkSheet workSheet = new WorkSheet();
                workSheet.ISheet = _iWorkBook.GetSheetAt(sheetIndex);
                workSheet.IWorkBook = _iWorkBook;
                workSheet.ICellStyle = _iCellStyle;
                return workSheet;
            }
            public WorkSheet GetSheet(string sheetName)
            {
                WorkSheet workSheet = new WorkSheet();
                workSheet.ISheet = _iWorkBook.GetSheet(sheetName);
                workSheet.IWorkBook = _iWorkBook;
                workSheet.ICellStyle = _iCellStyle;
                return workSheet;
            }
            /// <summary>
            /// 新建一个默认的样式对象
            /// </summary>
            private ICellStyle CreateDefaultStyle()
            {
                ICellStyle iStyle = _iWorkBook.CreateCellStyle();
                IFont iFont = _iWorkBook.CreateFont();
                iFont.FontName = "宋体";
                iFont.FontHeightInPoints = 12;
                iStyle.SetFont(iFont);
                return iStyle;
            }
            /// <summary>
            ///  新建一个样式对象
            /// </summary>
            public WorkStyle CreateStyle()
            {
                WorkStyle style = new WorkStyle();
                style.IStyle = _iWorkBook.CreateCellStyle();
                style.IWorkBook = _iWorkBook;
                style.Font = CreateFont();
                return style;
            }
            /// <summary>
            ///  新建一个字体对象
            /// </summary>
            private WorkFont CreateFont()
            {
                WorkFont font = new WorkFont();
                font.IFont = _iWorkBook.CreateFont();
                font.IWorkBook = _iWorkBook;
                font.Name = "宋体";
                font.Size = 12;
                return font;
            }
        }

        /// <summary>
        /// 工作表配置
        /// </summary>
        public class WorkSheet
        {
            private ISheet _iSheet = null;
            private IWorkbook _iWorkBook = null;
            private ICellStyle _iCellStyle = null;
            internal ISheet ISheet
            {
                get { return _iSheet; }
                set { _iSheet = value; }
            }
            internal IWorkbook IWorkBook
            {
                get { return _iWorkBook; }
                set { _iWorkBook = value; }
            }
            internal ICellStyle ICellStyle
            {
                get { return _iCellStyle; }
                set { _iCellStyle = value; }
            }
            /// <summary>
            ///  保护工作表
            /// </summary>
            public void Protect()
            {
                Protect("88850925");
            }
            public void Protect(string password)
            {
                _iSheet.ProtectSheet(password);
            }
            /// <summary>
            ///  获取索引
            /// </summary>
            public int GetIndex()
            {
                return _iWorkBook.GetSheetIndex(_iSheet);
            }
            /// <summary>
            ///  获取名称
            /// </summary>
            public string GetName()
            {
                return _iWorkBook.GetSheetName(GetIndex());
            }
            /// <summary>
            /// 获取或设置显隐性
            /// </summary>
            public bool Hide
            {
                get { return _iWorkBook.IsSheetHidden(GetIndex()); }
                set { _iWorkBook.SetSheetHidden(GetIndex(), value ? SheetState.Hidden : SheetState.Visible); }
            }
            /// <summary>
            /// 操作指定行
            /// </summary>
            public WorkRow GetRow(int rowIndex)
            {
                WorkRow workRow = new WorkRow();
                if (_iSheet.GetRow(rowIndex) == null)
                    _iSheet.CreateRow(rowIndex);
                workRow.IRow = _iSheet.GetRow(rowIndex);
                workRow.IWorkBook = _iWorkBook;
                workRow.ICellStyle = _iCellStyle;
                return workRow;
            }
            /// <summary>
            /// 操作指定列
            /// </summary>
            public WorkColumn GetColumn(int columnIndex)
            {
                WorkColumn workColumn = new WorkColumn();
                workColumn.IColumn = _iSheet;
                workColumn.ColumnIndex = columnIndex;
                workColumn.IWorkBook = _iWorkBook;
                workColumn.ICellStyle = _iCellStyle;
                return workColumn;
            }
            /// <summary>
            /// 操作指定单元格
            /// </summary>
            public WorkCell GetCell(CellAddressRange cellAddressRange)
            {
                MergeCells(cellAddressRange);
                return GetCell(cellAddressRange.RowFrom, cellAddressRange.ColumnFrom);
            }
            public WorkCell GetCell(int rowIndex, int columnIndex)
            {
                WorkCell workCell = new WorkCell();
                if (_iSheet.GetRow(rowIndex) == null)
                    _iSheet.CreateRow(rowIndex);
                if (_iSheet.GetRow(rowIndex).GetCell(columnIndex) == null)
                {
                    _iSheet.GetRow(rowIndex).CreateCell(columnIndex);
                    _iSheet.GetRow(rowIndex).GetCell(columnIndex).CellStyle = _iCellStyle;
                }
                workCell.ICell = _iSheet.GetRow(rowIndex).GetCell(columnIndex);
                workCell.IWorkBook = _iWorkBook;
                return workCell;
            }
            /// <summary>
            /// 设置页眉
            /// </summary>
            public void SetHeader(int align, string content)
            {
                switch (align)
                {
                    case Constant.Align.LEFT:
                        _iSheet.Header.Left = content;
                        break;
                    case Constant.Align.CENTER:
                        _iSheet.Header.Center = content;
                        break;
                    case Constant.Align.RIGHT:
                        _iSheet.Header.Right = content;
                        break;
                }
            }
            /// <summary>
            /// 设置页脚
            /// </summary>
            public void SetFooter(int align, string content)
            {
                switch (align)
                {
                    case Constant.Align.LEFT:
                        _iSheet.Footer.Left = content;
                        break;
                    case Constant.Align.CENTER:
                        _iSheet.Footer.Center = content;
                        break;
                    case Constant.Align.RIGHT:
                        _iSheet.Footer.Right = content;
                        break;
                }
            }
            /// <summary>
            /// 设置边距（Excel中的数值对应为参数值的2.5倍）
            /// </summary>
            public void SetMargin(double leftMargin, double rightMargin, double topMargin, double bottomMargin)
            {
                _iSheet.SetMargin(MarginType.LeftMargin, leftMargin);
                _iSheet.SetMargin(MarginType.RightMargin, rightMargin);
                _iSheet.SetMargin(MarginType.TopMargin, topMargin);
                _iSheet.SetMargin(MarginType.BottomMargin, bottomMargin);
            }
            /// <summary>
            ///  设置纸张大小
            /// </summary>
            public void SetPaperSize(short paperSize)
            {
                _iSheet.PrintSetup.PaperSize = paperSize;
            }
            /// <summary>
            ///  设置所有行的默认高度
            /// </summary>
            public void SetDefalutRowHeight(int height)
            {
                _iSheet.DefaultRowHeight = (short)(height * 20);
            }
            /// <summary>
            ///  设置所有列的默认宽度
            /// </summary>
            public void SetDefaultColumnWidth(int width)
            {
                for (int intColumnIndex = 0; intColumnIndex < 250; intColumnIndex++)
                    GetColumn(intColumnIndex).Width = width;
            }
            /// <summary>
            ///  定义名称
            /// </summary>
            public void AddName(string strName, string strFormula)
            {
                IName name = _iWorkBook.CreateName();
                name.NameName = strName;
                name.RefersToFormula = _iSheet.SheetName + "!" + strFormula;
            }
            /// <summary>
            /// 设置数据有效性(显示序列)
            /// </summary>
            public void AddValidationDataForExplicitList(CellAddressRange cellAddressRange, string[] strDataList)
            {
                CellRangeAddressList regions = new CellRangeAddressList(cellAddressRange.RowFrom, cellAddressRange.RowTo, cellAddressRange.ColumnFrom, cellAddressRange.ColumnTo);
                DVConstraint constraint = DVConstraint.CreateExplicitListConstraint(strDataList);
                HSSFDataValidation dataValidate = new HSSFDataValidation(regions, constraint);
                _iSheet.AddValidationData(dataValidate);
            }
            /// <summary>
            /// 设置数据有效性(公式序列)
            /// </summary>
            public void AddValidationDataForFormulaList(CellAddressRange cellAddressRange, string strFormula)
            {
                CellRangeAddressList regions = new CellRangeAddressList(cellAddressRange.RowFrom, cellAddressRange.RowTo, cellAddressRange.ColumnFrom, cellAddressRange.ColumnTo);
                DVConstraint constraint = DVConstraint.CreateFormulaListConstraint(strFormula);
                HSSFDataValidation dataValidate = new HSSFDataValidation(regions, constraint);
                _iSheet.AddValidationData(dataValidate);
            }
            /// <summary>
            ///  合并单元格
            /// </summary>
            public void MergeCells(CellAddressRange cellAddressRange)
            {
                _iSheet.AddMergedRegion(new CellRangeAddress(cellAddressRange.RowFrom, cellAddressRange.RowTo, cellAddressRange.ColumnFrom, cellAddressRange.ColumnTo));
            }
            /// <summary>
            ///  冻结窗口
            /// </summary>
            public void FreezePane(int columnSplit, int rowSplit)
            {
                _iSheet.CreateFreezePane(columnSplit, rowSplit);
            }
            /// <summary>
            ///  冻结窗口(行)
            /// </summary>
            public void FreezePaneForRow(int rowSplit)
            {
                _iSheet.CreateFreezePane(0, rowSplit);
            }
            /// <summary>
            ///  冻结窗口(列)
            /// </summary>
            public void FreezePaneForColumn(int columnSplit)
            {
                _iSheet.CreateFreezePane(columnSplit, 0);
            }
        }

        /// <summary>
        /// 行配置
        /// </summary>
        public class WorkRow
        {
            private IRow _iRow = null;
            private IWorkbook _iWorkBook = null;
            private ICellStyle _iCellStyle = null;
            private WorkStyle _workStyle = new WorkStyle();
            internal IRow IRow
            {
                get { return _iRow; }
                set { _iRow = value; }
            }
            internal IWorkbook IWorkBook
            {
                get { return _iWorkBook; }
                set { _iWorkBook = value; }
            }
            internal ICellStyle ICellStyle
            {
                get { return _iCellStyle; }
                set { _iCellStyle = value; }
            }
            /// <summary>
            ///  获取或设置高度
            /// </summary>
            public int Height
            {
                get { return _iRow.Height / 20; }
                set { _iRow.Height = (short)(value * 20); }
            }
            /// <summary>
            /// 获取或设置显隐性
            /// </summary>
            public bool Hide
            {
                get { return _iRow.ZeroHeight; }
                set { _iRow.ZeroHeight = value; }
            }
            /// <summary>
            /// 获取或设置样式
            /// </summary>
            public WorkStyle Style
            {
                get
                {
                    if (_iRow.RowStyle == null)
                        _iRow.RowStyle = _iCellStyle;
                    _workStyle.IStyle = _iRow.RowStyle;
                    _workStyle.IWorkBook = _iWorkBook;
                    return _workStyle;
                }
                set { _workStyle = value; _iRow.RowStyle = _workStyle.IStyle; }
            }
        }

        /// <summary>
        /// 列配置
        /// </summary>
        public class WorkColumn
        {
            private ISheet _iColumn = null;
            private int _columnIndex = -1;
            private IWorkbook _iWorkBook = null;
            private ICellStyle _iCellStyle = null;
            private WorkStyle _workStyle = new WorkStyle();
            internal ISheet IColumn
            {
                get { return _iColumn; }
                set { _iColumn = value; }
            }
            internal int ColumnIndex
            {
                get { return _columnIndex; }
                set { _columnIndex = value; }
            }
            internal IWorkbook IWorkBook
            {
                get { return _iWorkBook; }
                set { _iWorkBook = value; }
            }
            internal ICellStyle ICellStyle
            {
                get { return _iCellStyle; }
                set { _iCellStyle = value; }
            }
            /// <summary>
            ///  自适应宽度
            /// </summary>
            public void AutoWidth()
            {
                _iColumn.AutoSizeColumn(_columnIndex, true);
            }
            /// <summary>
            ///  获取或设置宽度
            /// </summary>
            public int Width
            {
                get { return _iColumn.GetColumnWidth(_columnIndex) / 256; }
                set { _iColumn.SetColumnWidth(_columnIndex, value * 256); }
            }
            /// <summary>
            /// 获取或设置显隐性
            /// </summary>
            public bool Hide
            {
                get { return _iColumn.IsColumnHidden(_columnIndex); }
                set { _iColumn.SetColumnHidden(_columnIndex, value); }
            }
            /// <summary>
            /// 获取或设置样式
            /// </summary>
            public WorkStyle Style
            {
                get
                {
                    if (_iColumn.GetColumnStyle(_columnIndex) == null)
                        _iColumn.SetDefaultColumnStyle(_columnIndex, _iCellStyle);
                    _workStyle.IStyle = _iColumn.GetColumnStyle(_columnIndex);
                    _workStyle.IWorkBook = _iWorkBook;
                    return _workStyle;
                }
                set { _workStyle = value; _iColumn.SetDefaultColumnStyle(_columnIndex, _workStyle.IStyle); }
            }
        }

        /// <summary>
        /// 单元格配置
        /// </summary>
        public class WorkCell
        {
            private ICell _iCell = null;
            private IWorkbook _iWorkBook = null;
            private WorkStyle _workStyle = new WorkStyle();
            internal ICell ICell
            {
                get { return _iCell; }
                set { _iCell = value; }
            }
            internal IWorkbook IWorkBook
            {
                get { return _iWorkBook; }
                set { _iWorkBook = value; }
            }
            /// <summary>
            /// 获取值
            /// </summary>
            public string GetValue()
            {
                return GetValue(null);
            }
            public string GetValue(String strDataFormat)
            {
                try
                {
                    switch (_iCell.CellType)
                    {
                        case CellType.Numeric:
                            if (DateUtil.IsCellDateFormatted(_iCell))
                                return _iCell.DateCellValue.ToString(strDataFormat == null ? "yyyy-MM-dd" : strDataFormat);
                            else
                                return _iCell.ToString();
                        case CellType.Formula:
                            return _iCell.NumericCellValue.ToString();
                        default:
                            return _iCell.ToString();
                    }
                }
                catch
                {
                    return _iCell.ToString();
                }
            }
            /// <summary>
            /// 设置值
            /// </summary>
            public void SetValue(string value)
            {
                _iCell.SetCellValue(value);
            }
            public void SetValue(DateTime value)
            {
                _iCell.SetCellValue(value);
            }
            public void SetValue(double value)
            {
                _iCell.SetCellValue(value);
            }
            /// <summary>
            /// 获取或设置公式
            /// </summary>
            public string Formula
            {
                get { return _iCell.CellFormula; }
                set { _iCell.CellFormula = value; }
            }
            /// <summary>
            /// 获取或设置批注
            /// </summary>
            public string Comment
            {
                get { return _iCell.CellFormula; }
                set { _iCell.CellFormula = value; }
            }
            /// <summary>
            /// 获取或设置样式
            /// </summary>
            public WorkStyle Style
            {
                get { _workStyle.IStyle = _iCell.CellStyle; _workStyle.IWorkBook = _iWorkBook; return _workStyle; }
                set
                {
                    _workStyle = value;
                    _iCell.CellStyle = _workStyle.IStyle;
                    if (_iCell.IsMergedCell)
                    {
                        int intMergedCount = _iCell.Sheet.NumMergedRegions;
                        for (int intIndex = intMergedCount - 1; intIndex >= 0; intIndex--)
                        {
                            CellRangeAddress cellRangeAddress = _iCell.Sheet.GetMergedRegion(intIndex);
                            if (cellRangeAddress.FirstRow <= _iCell.RowIndex && _iCell.RowIndex <= cellRangeAddress.LastRow &&
                                cellRangeAddress.FirstColumn <= _iCell.ColumnIndex && _iCell.ColumnIndex <= cellRangeAddress.LastColumn)
                            {
                                for (int intRowIndex = cellRangeAddress.FirstRow; intRowIndex <= cellRangeAddress.LastRow; intRowIndex++)
                                {
                                    if (_iCell.Sheet.GetRow(intRowIndex) == null)
                                        _iCell.Sheet.CreateRow(intRowIndex);
                                    for (int intColumnIndex = cellRangeAddress.FirstColumn; intColumnIndex <= cellRangeAddress.LastColumn; intColumnIndex++)
                                    {
                                        if (_iCell.Sheet.GetRow(intRowIndex).GetCell(intColumnIndex) == null)
                                            _iCell.Sheet.GetRow(intRowIndex).CreateCell(intColumnIndex);
                                        _iCell.Sheet.GetRow(intRowIndex).GetCell(intColumnIndex).CellStyle = _workStyle.IStyle;
                                    }
                                }
                                break;
                            }
                        }
                    }
                }
            }
        }

        /// <summary>
        /// 样式配置
        /// </summary>
        public class WorkStyle
        {
            private ICellStyle _iStyle = null;
            private IWorkbook _iWorkBook = null;
            private WorkFont _workFont = new WorkFont();
            internal ICellStyle IStyle
            {
                get { return _iStyle; }
                set { _iStyle = value; }
            }
            internal IWorkbook IWorkBook
            {
                get { return _iWorkBook; }
                set { _iWorkBook = value; }
            }
            /// <summary>
            /// 获取或设置锁定状态
            /// </summary>
            public bool Lock
            {
                get { return _iStyle.IsLocked; }
                set { _iStyle.IsLocked = value; }
            }
            /// <summary>
            /// 获取或设置自动换行
            /// </summary>
            public bool WrapText
            {
                get { return _iStyle.WrapText; }
                set { _iStyle.WrapText = value; }
            }
            /// <summary>
            /// 获取或设置数据格式
            /// </summary>
            public string DataFormat
            {
                get { return _iWorkBook.CreateDataFormat().GetFormat(_iStyle.DataFormat); }
                set { _iStyle.DataFormat = _iWorkBook.CreateDataFormat().GetFormat(value); }
            }
            /// <summary>
            /// 获取或设置颜色
            /// </summary>
            public short Color
            {
                get { return _iStyle.FillBackgroundColor; }
                set
                {
                    _iStyle.FillPattern = FillPattern.SolidForeground;
                    _iStyle.FillBackgroundColor = value;
                    _iStyle.FillForegroundColor = value;
                    Border = Constant.Border.THIN;
                }
            }
            /// <summary>
            /// 获取或设置边框样式
            /// </summary>
            public int Border
            {
                get { return (int)_iStyle.BorderLeft; }
                set
                {
                    _iStyle.BorderLeft = (BorderStyle)value;
                    _iStyle.BorderRight = (BorderStyle)value;
                    _iStyle.BorderTop = (BorderStyle)value;
                    _iStyle.BorderBottom = (BorderStyle)value;
                }
            }
            /// <summary>
            /// 获取或设置边框颜色
            /// </summary>
            public short BorderColor
            {
                get { return _iStyle.LeftBorderColor; }
                set
                {
                    _iStyle.LeftBorderColor = value;
                    _iStyle.RightBorderColor = value;
                    _iStyle.TopBorderColor = value;
                    _iStyle.BottomBorderColor = value;
                }
            }
            /// <summary>
            /// 获取或设置水平对齐方式
            /// </summary>
            public int Align
            {
                get { return (int)_iStyle.Alignment; }
                set { _iStyle.Alignment = (HorizontalAlignment)value; }
            }
            /// <summary>
            /// 获取或设置垂直对齐方式
            /// </summary>
            public int Valign
            {
                get { return (int)_iStyle.VerticalAlignment; }
                set { _iStyle.VerticalAlignment = (VerticalAlignment)value; }
            }
            /// <summary>
            /// 获取或设置字体
            /// </summary>
            public WorkFont Font
            {
                get { _workFont.IFont = _iStyle.GetFont(_iWorkBook); _workFont.IWorkBook = _iWorkBook; return _workFont; }
                set { _workFont = value; _iStyle.SetFont(_workFont.IFont); }
            }
        }

        /// <summary>
        /// 字体配置
        /// </summary>
        public class WorkFont
        {
            private IFont _iFont = null;
            private IWorkbook _iWorkBook = null;
            internal IFont IFont
            {
                get { return _iFont; }
                set { _iFont = value; }
            }
            internal IWorkbook IWorkBook
            {
                get { return _iWorkBook; }
                set { _iWorkBook = value; }
            }
            /// <summary>
            /// 获取或设置字体
            /// </summary>
            public string Name
            {
                get { return _iFont.FontName; }
                set { _iFont.FontName = value; }
            }
            /// <summary>
            /// 获取或设置字体粗细
            /// </summary>
            public bool Bold
            {
                get { return _iFont.IsBold; }
                set { _iFont.IsBold = value; }
            }
            /// <summary>
            /// 获取或设置字体颜色
            /// </summary>
            public short Color
            {
                get { return _iFont.Color; }
                set { _iFont.Color = value; }
            }
            /// <summary>
            /// 获取或设置字体大小
            /// </summary>
            public double Size
            {
                get { return _iFont.FontHeightInPoints; }
                set { _iFont.FontHeightInPoints = value; }
            }
        }

        /// <summary>
        /// 单元格地址范围
        /// </summary>
        public class CellAddressRange
        {
            private int _rowFrom = 0;
            private int _rowTo = 0;
            private int _columnFrom = 0;
            private int _columnTo = 0;
            public int RowFrom
            {
                get { return _rowFrom; }
                set { _rowFrom = value; }
            }
            public int RowTo
            {
                get { return _rowTo; }
                set { _rowTo = value; }
            }
            public int ColumnFrom
            {
                get { return _columnFrom; }
                set { _columnFrom = value; }
            }
            public int ColumnTo
            {
                get { return _columnTo; }
                set { _columnTo = value; }
            }
        }

        public static class Constant
        {
            /// <summary>
            /// 颜色选择
            /// </summary>
            public class Color : HSSFColor { }
            /// <summary>
            /// 边框样式选择
            /// </summary>
            public static class Border
            {
                public const int NONE = (int)BorderStyle.None;
                public const int DOTTED = (int)BorderStyle.Dotted;
                public const int THIN = (int)BorderStyle.Thin;
            }
            /// <summary>
            /// 水平对齐方式选择
            /// </summary>
            public static class Align
            {
                public const int LEFT = (int)HorizontalAlignment.Left;
                public const int CENTER = (int)HorizontalAlignment.Center;
                public const int RIGHT = (int)HorizontalAlignment.Right;
            }
            /// <summary>
            /// 垂直对齐方式选择
            /// </summary>
            public static class Valign
            {
                public const int TOP = (int)VerticalAlignment.Top;
                public const int CENTER = (int)VerticalAlignment.Center;
                public const int BOTTOM = (int)VerticalAlignment.Bottom;
            }
        }

        public static class Tools
        {
            /// <summary>
            /// 列索引转列名
            /// </summary>
            public static string ColumnIndexToName(int columnIndex)
            {
                if (columnIndex < 0)
                    return "A";
                List<string> chars = new List<string>();
                do
                {
                    if (chars.Count > 0) columnIndex--;
                    chars.Insert(0, ((char)(columnIndex % 26 + (int)'A')).ToString());
                    columnIndex = (int)((columnIndex - columnIndex % 26) / 26);
                } while (columnIndex > 0);
                return String.Join(string.Empty, chars.ToArray());
            }
            /// <summary>
            /// 列名转列索引
            /// </summary>
            public static int ColumnNameToIndex(string columnName)
            {
                if (!Regex.IsMatch(columnName.ToUpper(), @"[A-Z]+"))
                    return 0;
                int index = 0;
                char[] chars = columnName.ToUpper().ToCharArray();
                for (int i = 0; i < chars.Length; i++)
                {
                    index += ((int)chars[i] - (int)'A' + 1) * (int)Math.Pow(26, chars.Length - i - 1);
                }
                return index - 1;
            }
        }
    }
}
